package com.finapps.stopspend.dao;

import java.io.File;
import java.io.IOException;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class StopSpendSqlLiteHelper extends SQLiteOpenHelper {
	private Context context;
													  
	String sqlCreateTableTransactions = "CREATE TABLE Transactions(" +
										"id  integer PRIMARY KEY AUTOINCREMENT NOT NULL,"+
								     	"data nvarchar(8) NOT NULL,"+  //AAAAMMDD
								     	"hora nvarchar(4) NOT NULL,"+
								     	"descripcio nvarchar(255),"+
								     	"carrec decimal(10,2),"+
								     	"saldo decimal(10,2),"+
								     	"estalvi_mes decimal(10,2)"+
								     	")";
	String sqlCreateTableObjectius = "CREATE TABLE Objectius(" +
									 "id  integer PRIMARY KEY AUTOINCREMENT NOT NULL,"+
								     "data nvarchar(8) NOT NULL,"+  //AAAAMMDD
								     "nom nvarchar(20) NOT NULL,"+
								     "descripcio nvarchar(255) NOT NULL,"+
								     "saldo decimal(10,2),"+		
								     "falta decimal(10,2),"+		
								     "color integer"+		

							     	")";

	public StopSpendSqlLiteHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		newDatabaseTables(db);
			parserCsv(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS Transactions");
		db.execSQL("DROP TABLE IF EXISTS Objectius");

		newDatabaseTables(db);
		
	}

	private void newDatabaseTables(SQLiteDatabase db){
		db.execSQL(sqlCreateTableTransactions);
		db.execSQL(sqlCreateTableObjectius);

	}
	
	private void parserCsv(SQLiteDatabase db){
		
		List<String> lines;
		try {
			
			lines = IOUtils.readLines(context.getResources().getAssets().open("simula_cobros.csv"));
			for(String line:lines){
				String columnas[] =  line.split(",",-1);
					String hora = isHora( columnas[3]);
					String min = isHora( columnas[4]);

					db.execSQL("INSERT INTO Transactions(data,hora,descripcio,carrec,saldo,estalvi_mes) VALUES ('"+columnas[0] + columnas[1]+ columnas[2]+"'," +
						" '"+hora+min+"','"+columnas[5]+"','"+columnas[6]+"','"+columnas[7]+"','"+columnas[10]+"') " );
//		        System.out.println(columnas[0] + " " +columnas[1] + 
//		        		columnas[2] + " " +columnas[3] + columnas[4] + " " +columnas[5] + " "+ 
//		        		columnas[6] + " " +columnas[7] +" etc...");
	
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	
	//Corretgim hora si nomes te un nombre
	private String isHora(String hora){
		String horaCorrecte = "00";
		if(hora==null)
			return horaCorrecte;
		
//		if( Integer.parseInt(hora)<10 ){
//			horaCorrecte ="0"+ hora.charAt(0);
//		}
		
		return horaCorrecte;
	}
}
